[メモ]BigQueryのGREATEST関数の対象からNULLを除外する

GREATEST関数についてのメモです。

#Google Cloud (GCP)

#Google BigQuery

ニューシロ

2024.04.30

クラスメソッド株式会社データアナリティクス事業本部所属のニューシロです。
今回はGoogle CloudのサービスであるBigQueryにおけるGREATEST関数の記事です。

前提

GREATEST関数とは

GREATEST関数は、複数の引数の中から最大の値を返す関数です。

参考：Mathematical functions | BigQuery | Google Cloud - GREATEST

逆に、複数の引数の中から最小の値を返す関数LEAST関数もあります。

参考：Mathematical functions | BigQuery | Google Cloud - LEAST

ほぼ同じ挙動のため、今回はこちらもあわせて検証しています。

参考ブログ

GREATEST関数、LEAST関数については以下のブログが参考になりました。

GREATEST関数は、引数にNULLを1件でも含む場合はNULLを返します。
今回は、NULLを除く引数で比較できるように、少しSQLを変更してみます。

使ってみる

そのまま使用する

引数に1件でもNULLを含んでいるとNULLを返すことを確認します。
以下のSQLを使用して、4パターンの引数で比較してみます。

WITH master AS (
  SELECT 10 AS col_1, 20 AS col_2, 30 AS col_3
  UNION ALL
  SELECT NULL AS col_1, 20 AS col_2, 30 AS col_3
  UNION ALL
  SELECT NULL AS col_1, NULL AS col_2,30 AS col_3
  UNION ALL
  SELECT NULL AS col_1, NULL AS col_2, NULL AS col_3
)
SELECT
  *,
  GREATEST(col_1, col_2, col_3) AS greatest,
  LEAST(col_1, col_2, col_3) AS least
FROM master

SQL実行結果

+-------+-------+-------+----------+-------+
| col_1 | col_2 | col_3 | greatest | least |
+-------+-------+-------+----------+-------+
|    10 |    20 |    30 |       30 |    10 |
|  NULL |    20 |    30 |     NULL |  NULL |
|  NULL |  NULL |    30 |     NULL |  NULL |
|  NULL |  NULL |  NULL |     NULL |  NULL |
+-------+-------+-------+----------+-------+

ハイライト部分のレコードにおいて、GREATEST関数（またはLEAST関数）の引数にNULLが含まれているため、他の引数を無視してNULLを返しています。
NULLを除外した引数で比較した結果を返すように、少しSQLを変更してみます。

NULLを除外する

IFNULL関数を使用して、NULLを含む場合でも最大値を出力できるようにします。
GREATEST関数では、想定される引数の最小値をIFNULL関数の第二引数とします（LEAST関数では、想定される引数の最大値）。これにより、IFNULL関数によって置き換えられた値がGREATEST関数で最大値として選ばれることはありません。

WITH master AS (
  SELECT 10 AS col_1, 20 AS col_2, 30 AS col_3
  UNION ALL
  SELECT NULL AS col_1, 20 AS col_2, 30 AS col_3
  UNION ALL
  SELECT NULL AS col_1, NULL AS col_2,30 AS col_3
  UNION ALL
  SELECT NULL AS col_1, NULL AS col_2, NULL AS col_3
)
SELECT
  *,
  GREATEST(IFNULL(col_1, 0), IFNULL(col_2, 0), IFNULL(col_3, 0)) AS greatest,
  LEAST(IFNULL(col_1, 100), IFNULL(col_2, 100), IFNULL(col_3, 100)) AS least
FROM master

SQL実行結果

+-------+-------+-------+----------+-------+
| col_1 | col_2 | col_3 | greatest | least |
+-------+-------+-------+----------+-------+
|    10 |    20 |    30 |       30 |    10 |
|  NULL |    20 |    30 |       30 |    20 |
|  NULL |  NULL |    30 |       30 |    30 |
|  NULL |  NULL |  NULL |        0 |   100 |
+-------+-------+-------+----------+-------+

これで、NULLを除いた引数の内で最大値（最小値）を返すことができました。

しかし、最後のレコード（ハイライト箇所）ではGREATEST関数（またはLEAST関数）は引数が全てNULLですが、特定の値(0, 100)が返されてしまっています。
全ての引数がNULLならNULLを返すように、少しSQLを変更してみます。

NULLを除外し、かつ全ての引数がNULLならNULLを返す

IF文で条件分岐させましょう。

WITH master AS (
  SELECT 10 AS col_1, 20 AS col_2, 30 AS col_3
  UNION ALL
  SELECT NULL AS col_1, 20 AS col_2, 30 AS col_3
  UNION ALL
  SELECT NULL AS col_1, NULL AS col_2,30 AS col_3
  UNION ALL
  SELECT NULL AS col_1, NULL AS col_2, NULL AS col_3
)
SELECT
  *,
  IF(col_1 IS NULL AND col_2 IS NULL AND col_3 IS NULL,  NULL,
    GREATEST(IFNULL(col_1, 0), IFNULL(col_2, 0), IFNULL(col_3, 0))
    ) AS greatest,
  IF(col_1 IS NULL AND col_2 IS NULL AND col_3 IS NULL,  NULL,
    LEAST(IFNULL(col_1, 100), IFNULL(col_2, 100), IFNULL(col_3, 100))
    ) AS least
FROM master

SQL実行結果

+-------+-------+-------+----------+-------+
| col_1 | col_2 | col_3 | greatest | least |
+-------+-------+-------+----------+-------+
|    10 |    20 |    30 |       30 |    10 |
|  NULL |    20 |    30 |       30 |    20 |
|  NULL |  NULL |    30 |       30 |    30 |
|  NULL |  NULL |  NULL |     NULL |  NULL |
+-------+-------+-------+----------+-------+

これにより、NULLを含む場合は他の引数で比較し、また全ての引数がNULLのときはNULLを返すことができました。